//引入数据库
var mysql=require('mysql');
//创建数据库链接
var db=mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'tiantianpeiwan'
});

var json={
    "player":function (id,fn) {
        var sql=`select * from p_user where U_id="${id}"`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    "guanzhu":function (id,fn) {
        var sql=`SELECT COUNT(*) AS a FROM p_follow WHERE FO_puid="${id}"`;
        var sql2=`SELECT FO_uid FROM p_follow WHERE FO_puid="${id}"`;
        db.query(sql,function (err,data) {

            if (err==null){
                var json={a:data[0].a};

                db.query(sql2,function (err,data){

                    data.unshift(json);

                    fn(err,data)
                })
            }
        })
    },
    "follow":function ({id,uid},fn) {
        var sql=`SELECT U_co FROM p_user WHERE U_id='${id}'`;
        db.query(sql,function (err,data) {
            if (err==null){
                let uCo=data[0].U_co;
                var sql2=`INSERT INTO p_follow VALUES (NULL,${uCo},${uid},${id})`;
                db.query(sql2,function (err,data){
                    fn(err,data)
                })
            }
        })
    },
    "follow2":function ({id,uid},fn) {
        var sql=`DELETE FROM p_follow WHERE FO_uid='${uid}' AND FO_puid='${id}'`;
        db.query(sql,function (err,data) {
                    fn(err,data)
        })
    },
    "lw":function (fn) {
        var sql=`SELECT * FROM p_giftattribute`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    "game":function (id,fn) {
        var sql=`SELECT * FROM p_user a LEFT JOIN p_playuser b ON a.U_co=b.U_co LEFT JOIN p_game c ON b.G_id=c.G_id WHERE U_id="${id}";`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    "cishu":function (id,fn) {
        var sql=`SELECT *,COUNT(*) AS COUNT FROM p_jdtable AS a LEFT JOIN p_user AS b ON a.U_co1=b.U_co WHERE U_id='${id}' AND JD_puon='已评价'`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    "pl":function (id,fn) {
        var sql=`SELECT *
FROM p_user AS a LEFT JOIN  p_jdtable AS b ON a.U_co=b.U_co LEFT JOIN p_evaluate AS c ON b.JD_co=c.EV_jdco 
WHERE b.U_co1=(SELECT U_co 
FROM p_user
WHERE U_id='${id}') AND JD_puon='已评价'
`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    "getPage":function (id,fn) {
        var sql=`SELECT COUNT(JD_co) AS num 
FROM p_jdtable
WHERE U_co1=(SELECT U_co 
FROM p_user
WHERE U_id='${id}') AND JD_puon='已评价'
`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    "getPage2":function (id,fn) {
        var sql=`SELECT COUNT(GF_id) AS num 
FROM p_gift
WHERE U_co1=(SELECT U_co 
FROM p_user
WHERE U_id='${id}')
`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    "pageLw":function ({id,num},fn) {
        var num1=num;
        var sql=` SELECT *
FROM p_gift JOIN p_giftattribute JOIN p_user
WHERE  p_gift.GF_gagift=p_giftattribute.GA_id AND p_gift.U_co1=(SELECT U_co
FROM p_user
WHERE U_id='${id}') AND p_gift.U_co=p_user.U_co limit ${(num1-1)*5},5
`;
        db.query(sql,function (err,data) {

            fn(err,data)
        })
    },
     "ranking":function (id,fn) {
            var sql=` SELECT *,SUM(GF_num*GA_price) AS money
FROM (SELECT U_co,GF_gagift,GF_num  
FROM p_gift
WHERE U_co1=(SELECT U_co
FROM p_user
WHERE U_id='${id}')) AS a LEFT JOIN  p_user AS b 
ON a.U_co=b.U_co LEFT JOIN p_giftattribute AS c ON a.GF_gagift=c.GA_id 
GROUP BY U_names
ORDER BY money DESC
LIMIT 7
    `;
            db.query(sql,function (err,data) {
                fn(err,data)
            })
        },
    "lwGive":function ({id,uid,name,date,num},fn) {
        var sql=` SELECT GA_id,GA_price FROM p_giftattribute WHERE GA_name='${name}'
    `;
        var sql3=`SELECT U_co FROM p_user WHERE U_id='${uid}'`;
        var sql4=`SELECT U_co FROM p_user WHERE U_id='${id}'`;


        db.query(sql,function (err,data) {
            if (err==null){
                var GFid=data[0].GA_id;
                db.query(sql3,function (err,data){
                   if (err==null){
                       var uCo=data[0].U_co;
                       db.query(sql4,function (err,data){
                           var uCo1=data[0].U_co;
                           var sql2=`INSERT INTO p_gift(U_co,U_co1,GF_gagift,GF_data,GF_num) VALUES ('${uCo}','${uCo1}',${GFid},'${date}',${num})`;
                           db.query(sql2,function (err,data){
                                fn(err,data)
                           })
                       })
                   }
                })
            }
            // if (err==null){
            //     var GFid=data[0].GA_id;
            //     var sql2=`INSERT INTO p_gift(U_co,U_co1,GF_gagift,GF_data,GF_num) VALUES ('${uid}','${id}',${GFid},'${date}',${num})`;
            //     db.query(sql2,function (err,data) {
            //        fn(err,data)
            //     })
            // }
        })
    },






    //四阶段
    "users":function (id,fn) {
        var sql=`SELECT U_id,U_falsename,U_tel,U_date,U_ban FROM p_user WHERE U_player='0'`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    //搜索
    "search":function (id,fn) {
        var sql=`SELECT U_id,U_falsename,U_tel,U_date FROM p_user WHERE U_id LIKE '%${id}%' AND U_player='0'`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    //个人消费情况
    "details":function (id,fn) {

        var sql=`SELECT JD_money
FROM p_jdtable
WHERE U_co=(SELECT U_co
FROM p_user WHERE U_id='${id}') AND JD_puon='已评价'`;
        var sql2=`SELECT SUM(GF_num*GA_price) AS money
FROM (SELECT U_co,GF_gagift,GF_num  
FROM p_gift
WHERE U_co=(SELECT U_co
FROM p_user
WHERE U_id='${id}')) AS a LEFT JOIN  p_user AS b 
ON a.U_co=b.U_co LEFT JOIN p_giftattribute AS c ON a.GF_gagift=c.GA_id`;
        db.query(sql,function (err,data) {
            if (data.length==0){
                data.push({JD:0});
                let json=data[0];
                if (err==null){
                    db.query(sql2,function (err,data){
                        data.push(json);
                        fn(err,data)
                    })
                }
            } else {
                let json={JD:data[0].JD_money};
                if (err==null){
                    db.query(sql2,function (err,data){
                        data.push(json);
                        fn(err,data)
                    })
                }

            }

        })
    },
    //饼状图
    "cake":function (id,fn) {

        var sql=`SELECT SUM(JD_money) AS money_one FROM p_jdtable WHERE PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) , DATE_FORMAT( JD_datetimes, '%Y%m' ) ) =1 AND JD_puon='已评价'`;
        var sql2=`SELECT SUM(GF_num*GA_price) AS money_two
FROM (SELECT GF_gagift,GF_num  
FROM p_gift
WHERE PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) , DATE_FORMAT( GF_data, '%Y%m' ) ) =1) AS a 
LEFT JOIN p_giftattribute AS c ON a.GF_gagift=c.GA_id`;
        db.query(sql,function (err,data) {
            let json={JD:data[0].money_one};
            if (err==null){
                db.query(sql2,function (err,data){
                    data.push(json);
                    fn(err,data)
                })
            }
        })
    },
    //折线图
    "polygonal":function (id,fn) {

        var sql=`SELECT MONTH(JD_datetimes) AS times,SUM(JD_money) AS money
FROM p_jdtable
WHERE JD_puon='已评价'
GROUP BY MONTH(JD_datetimes)`;
        var sql2=`SELECT MONTH(GF_data) AS time2,SUM(GF_num*GA_price) AS money2
FROM (SELECT GF_gagift,GF_num,GF_data  
FROM p_gift
) AS a 
LEFT JOIN p_giftattribute AS c ON a.GF_gagift=c.GA_id
GROUP BY MONTH(GF_data)`;
        db.query(sql,function (err,data) {
                for (let i=0;i<12;i++){
                    if (data.length<i+1){
                        data.push({times: i, money: 0});
                    }
                    if (data[i].times!=i+1) {
                        data.splice(i, 0, {times: i+1, money: 0});
                    }
                }
            let arr=data;
            if (err==null){
                db.query(sql2,function (err,data){
                    for (let i=0;i<12;i++){
                        if (data.length<i+1){
                            data.push({time2: i, money2: 0});
                        }
                        if (data[i].time2!=i+1) {
                            data.splice(i, 0, {time2: i+1, money2: 0});
                        }
                    }

                    let data1=data;
                    // data.push(json);
                    let data2={a:arr,b:data1};
                    fn(err,data2)
                })
            }
        })
    },
    //拉入黑名单
    "blacklist":function (id,fn) {
        var sql1=`SELECT U_ban FROM p_user WHERE U_id='${id}'`;
        var sql=`UPDATE p_user SET U_ban='是' WHERE U_id='${id}'`;
        var sql2=`UPDATE p_user SET U_ban='否' WHERE U_id='${id}'`;
        db.query(sql1,function (err,data) {
            if (data[0].U_ban=='是'){
                db.query(sql2,function (err,data) {
                    fn(err,1)
                })
            } else {
                db.query(sql,function (err,data){
                    fn(err,2)
                })
            }
        })
    },

};


module.exports=json;